package com.luoyi.util.cc;

import cn.hutool.core.map.MapBuilder;
import cn.hutool.core.map.MapUtil;
import cn.hutool.core.util.StrUtil;
import cn.hutool.poi.excel.ExcelReader;
import cn.hutool.poi.excel.ExcelUtil;

import java.util.HashMap;
import java.util.List;
import java.util.Map;

/**
 * 智慧客服：新增坐席sql生成
 *
 * @author xt
 */
public class AddAccountSqlGenerate {

    private static final String INSERT_SQL = "INSERT INTO ICS_ACCOUNT \n" +
            "\t(ID, ACCOUNT_ID, ACCOUNT_NAME, ONLINE_STATUS, TENANT_ID, DELETED,\n" +
            "\t\"LANGUAGE\", IS_ROBOT, BELONG_TYPE, ACCOUNT_NO, ENTERPRISE_CODE,\n" +
            "\tACCOUNT_NUMBER, ACCOUNT_PASSWORD, EXTENSION_PASSWORD, TELEPHONE,\n" +
            "\tEMAIL, CALL_SKILL_VALUE, WEB_SKILL_VALUE, IS_LOGIN, PRIORITY,\n" +
            "\tDEPARTMENT_ID,DEPART_BP,DEPARTMENT_CODE) \n" +
            "VALUES ({id},'{accountId}','{accountName}','4','1791359997934452736',0,\n" +
            "\t\tNULL, NULL, 1, '{accountNo}', '200001',\n" +
            "\t\t'{accountNumber}','ZQ7h5@Bt#Ct8','1qaz2wsx#EDC!@#$',NULL,\n" +
            "\t\tNULL, NULL, NULL,'0', NULL,\n" +
            "\t'{departmentId}', '{departBp}', '{departmentCode}');\n";

    /**
     * 事业部
     */
    private final static Map<String, String> DEPART_BP_MAP = new HashMap<>(16);
    static{
        DEPART_BP_MAP.put("农机事业部", "00030990 0900008044 00000030,00030990");
        DEPART_BP_MAP.put("新材事业部", "00032584 0900019244 00000030,00032584");
    }


    public static void main(String[] args) {
        // 读取excel文件并遍历Sheet1的行，复制每一行内容并新增一行在其下方
        ExcelReader reader = ExcelUtil.getReader("D:\\fileTest\\1.xlsx");
        reader.setSheet("Sheet1");
        List<List<Object>> read = reader.read(1);
        reader.close();
        int id = 170;

        for (List<Object> objects : read) {
            String accountNo = objects.get(0).toString();
            String accountName = objects.get(1).toString();
            String accountId = objects.get(2).toString();
            String departmentName = objects.get(3).toString();
            String accountNumber = objects.get(6).toString();
            String departStr = DEPART_BP_MAP.get(departmentName);
            String[] split = departStr.split(" ");
            String departmentId = split[0];
            String departBp = split[1];
            String departmentCode = split[2];
            MapBuilder<String, Object> builder = MapUtil.builder(new HashMap<>(16));
            builder.put("id", id ++ )
                    .put("accountId", accountId)
                    .put("accountName", accountName)
                    .put("accountNo", accountNo)
                    .put("accountNumber", accountNumber)
                    .put("departmentId", departmentId)
                    .put("departBp", departBp)
                    .put("departmentCode", departmentCode);
//            System.out.println("map=" + JSON.toJSONString(builder.map()));
            String format = StrUtil.format(INSERT_SQL, builder.map());
            System.out.println(format);
        }
    }






}
